--Drop database BeautyClinic
--go

Create Database BeautyClinic 
go
use BeautyClinic
go
CREATE TABLE DBO.Customer (		
	CustID char(10),	
	CustName varchar(70) not null,	
	Gender bit not null,	
	Addr varchar(100),	
	Email varchar(50),	
	DOB date,
	Picture varchar(200),
	DateCreated date not null default(getdate()),
	UserCreated varchar(20) not null,
	DateModified date,
	UserModified varchar(20),
	PRIMARY KEY (CustID))	

go
CREATE TABLE DBO.Staff (	
	StaffID char(7),
	StaffName varchar(50) not null,
	Addr varchar(100) not null,
	Active bit not null,
	IsLock bit not null,
	Picture varchar(200),
	DateCreated date not null default(getdate()),
	UserCreated varchar(20) not null ,
	DateModified date,
	UserModified varchar(20),
	PRIMARY KEY (StaffID))
go
CREATE TABLE DBO.Category (	
	CatID char(6),
	CatName varchar(50) not null unique,
	[Desc] varchar(200),
	Facilities int not null,
	IsLock bit not null,
	PRIMARY KEY (CatID))

go
CREATE TABLE DBO.Services (	
	ServiceID char(9),
	ServiceName varchar(50) not null unique,
	CatID char(6) FOREIGN KEY REFERENCES Category(CatID) not null,
	Price money not null,
	[Desc] varchar(200),
	IsLock bit not null,
	PRIMARY KEY (ServiceID))
go
CREATE TABLE DBO.Specialist (	
	SpecID int identity(1,1),
	StaffID char(7) FOREIGN KEY REFERENCES Staff(StaffID) not null,
	ServiceID char(9) FOREIGN KEY REFERENCES Services(ServiceID) not null,
	PRIMARY KEY (SpecID))

go
CREATE TABLE DBO.AppMaster (	
	AppID char(15),
	AppDate date default(getdate()) not null,
	Status varchar(10) default('Processing') not null,
	CustID char(10) FOREIGN KEY REFERENCES Customer(CustID) not null,
	DateCreated date not null default(getdate()),
	UserCreated varchar(20) not null,
	DateModified date,
	UserModified varchar(20),
	PRIMARY KEY (AppID))
go
CREATE TABLE DBO.AppDetails (	
	AppDID char(18),
	AppID char(15) FOREIGN KEY REFERENCES AppMaster(AppID) not null,
	ServiceID char(9) FOREIGN KEY REFERENCES Services(ServiceID) not null,
	StaffID char(7) FOREIGN KEY REFERENCES Staff(StaffID) not null,
	Slot int not null,
	Status nvarchar(8) default('Pending') not null,
	DateCreated date not null default(getdate()),
	UserCreated varchar(20) not null,
	DateModified date,
	UserModified varchar(20),
	PRIMARY KEY (AppDID))
go
CREATE TABLE DBO.Bill (	
	BillID char(14),
	BillDate date not null default(getdate()),
	CustID char(10) FOREIGN KEY REFERENCES Customer(CustID) not null,
	AppID char(15) FOREIGN KEY REFERENCES AppMaster(AppID) not null,
	TotalAmount money not null,
	DateCreated date not null default(getdate()),
	UserCreated varchar(20) not null ,
	DateModified date,
	UserModified varchar(20),
	PRIMARY KEY (BillID))
go
CREATE TABLE DBO.Account (	
	UserName varchar(20) unique,
	[Password] varchar(20) not null,
	[Role] varchar(13) not null,
	Active bit not null,
	PRIMARY KEY (UserName))
go


-- Create View

Create view vw_staff_services
AS
SELECT        dbo.Staff.StaffID, dbo.Staff.StaffName, Services.ServiceName, dbo.Staff.Active,dbo.Staff.IsLock
FROM            dbo.Services INNER JOIN
                         dbo.Specialist ON dbo.Services.ServiceID = dbo.Specialist.ServiceID INNER JOIN
                         dbo.Staff ON dbo.Specialist.StaffID = dbo.Staff.StaffID
go
--
Create view vw_cate_services
AS
SELECT        dbo.Category.CatID, dbo.Category.CatName, dbo.Services.ServiceName, dbo.Services.ServiceID, dbo.Services.IsLock, dbo.Services.Price
FROM            dbo.Category INNER JOIN
                         dbo.Services ON dbo.Category.CatID = dbo.Services.CatID
go
Create view vw_cust_appmaster
AS
SELECT        dbo.AppMaster.AppID, dbo.AppMaster.AppDate, dbo.AppMaster.Status, dbo.Customer.CustID, dbo.Customer.CustName 
FROM            dbo.AppMaster INNER JOIN
                         dbo.Customer ON dbo.AppMaster.CustID = dbo.Customer.CustID

go
Create view vw_cat_app
AS
SELECT  App_Cat.*, dbo.Category.CatName,dbo.Category.Facilities
From
	(SELECT App.*, dbo.Services.ServiceName, dbo.Services.CatID
	FROM (SELECT dbo.AppMaster.AppID, dbo.AppDetails.ServiceID, dbo.AppDetails.Status
		FROM   dbo.AppMaster INNER JOIN dbo.AppDetails ON dbo.AppMaster.AppID = dbo.AppDetails.AppID) As App
		INNER JOIN dbo.Services ON App.ServiceID = dbo.Services.ServiceID) As App_Cat
		INNER JOIN dbo.Category ON App_Cat.CatID = dbo.Category.CatID
go
Create view vw_cust_bill
AS
SELECT        dbo.Bill.BillID, dbo.Bill.AppID, dbo.Bill.BillDate, dbo.Bill.TotalAmount, dbo.Customer.CustID, dbo.Customer.CustName 
FROM            dbo.Bill INNER JOIN
                         dbo.Customer ON dbo.Bill.CustID = dbo.Customer.CustID
go
create view vw_full_app
AS
SELECT App_Ser_Cat_Staff.* , dbo.Customer.CustName
FROM
	(SELECT App_Ser_Cat.*, dbo.Staff.StaffName
	FROM
		(SELECT  App_Ser.*, dbo.Category.CatName,dbo.Category.Facilities
		From
			(SELECT App.*, dbo.Services.ServiceName, dbo.Services.CatID, dbo.Services.Price
			FROM (SELECT dbo.AppMaster.AppID, dbo.AppMaster.Status as SttMaster, dbo.AppMaster.AppDate, dbo.AppMaster.CustID, AppDetails.AppDID, dbo.AppDetails.ServiceID, dbo.AppDetails.StaffID, dbo.AppDetails.Slot , dbo.AppDetails.Status as SttDetail
				FROM   dbo.AppMaster LEFT OUTER JOIN dbo.AppDetails ON dbo.AppMaster.AppID = dbo.AppDetails.AppID) As App
				LEFT OUTER JOIN dbo.Services ON App.ServiceID = dbo.Services.ServiceID) As App_Ser
				LEFT OUTER JOIN dbo.Category ON App_Ser.CatID = dbo.Category.CatID) As App_Ser_Cat
				LEFT OUTER JOIN dbo.Staff ON App_Ser_Cat.StaffID = dbo.Staff.StaffID) As App_Ser_Cat_Staff
				LEFT OUTER JOIN dbo.Customer On App_Ser_Cat_Staff.CustID = dbo.Customer.CustID 


go
-- Create proc
CREATE PROC LargestCustID
	@ID char(10) OUTPUT
AS
	BEGIN
		SELECT @ID =  (SELECT TOP 1 CustID FROM Customer ORDER BY custID DESC)
	END
go

-- Insert Data

-- Account Table
INSERT INTO ACCOUNT VALUES ('admin', 123, 'Administrator', 'true')
INSERT INTO ACCOUNT VALUES ('cash', 123, 'Cashier', 'true')
INSERT INTO ACCOUNT VALUES ('cust', 123, 'Customer', 'true')
INSERT INTO ACCOUNT VALUES ('custlock', 123, 'Customer', 'false')
INSERT INTO ACCOUNT VALUES ('per', 123, 'Personel', 'true')
INSERT INTO ACCOUNT VALUES ('ser', 123, 'Service', 'true')
go

-- Staff Table
INSERT INTO Staff(StaffID, StaffName,Addr, Active, IsLock,UserCreated) VALUES ('Emp0001', 'Jack Lee (No Active)', 'No address', 'FALSE', 'FALSE', 'admin')
INSERT INTO Staff(StaffID, StaffName,Addr, Active, IsLock,UserCreated) VALUES ('Emp0002', 'Alice Ann (Lock)', 'London', 'FALSE', 'TRUE', 'admin')
INSERT INTO Staff(StaffID, StaffName,Addr, Active, IsLock,UserCreated) VALUES ('Emp0003', 'John Murphy', 'Wales', 'TRUE', 'FALSE', 'admin')
INSERT INTO Staff(StaffID, StaffName,Addr, Active, IsLock,UserCreated) VALUES ('Emp0004', 'Daisy Doel', 'Paris', 'TRUE', 'FALSE', 'admin')
INSERT INTO Staff(StaffID, StaffName,Addr, Active, IsLock,UserCreated) VALUES ('Emp0005', 'Knight Chritian', 'Rome', 'TRUE', 'FALSE', 'admin')
INSERT INTO Staff(StaffID, StaffName,Addr, Active, IsLock,UserCreated) VALUES ('Emp0006', 'Alex Bumr', 'Sai Gon', 'TRUE', 'FALSE', 'admin')
INSERT INTO Staff(StaffID, StaffName,Addr, Active, IsLock,UserCreated) VALUES ('Emp0007', 'Well Comprah', 'Nice', 'TRUE', 'FALSE', 'admin')
INSERT INTO Staff(StaffID, StaffName,Addr, Active, IsLock,UserCreated) VALUES ('Emp0008', 'Handy Tom', 'Bangkok', 'TRUE', 'FALSE', 'admin')
go

-- Category Table
INSERT INTO Category VALUES ('Cat001', 'Cat Lock', 'Cat is locked to test', '5', 'TRUE')
INSERT INTO Category VALUES ('Cat002', 'Massage', 'Feeling the need to unwind? ', '15', 'FALSE')
INSERT INTO Category VALUES ('Cat003', 'Nails', 'Whether you have a party to go to or if you like your nails to be immaculate 24/7', '15', 'FALSE')
INSERT INTO Category VALUES ('Cat004', 'Tanning', 'We use St Tropez exclusively in Glamour beauty clinic to give you that flawless and professional looking tan', '15', 'FALSE')
INSERT INTO Category VALUES ('Cat005', 'Waxing', 'We use Australian Bodycare in our waxing treatments as it contains Tea Tree Oil', '15', 'FALSE')
go

-- Service Table
INSERT INTO Services VALUES ('Cat001.01', 'Service 1 of Cat Lock ', 'Cat001', '100000', 'No desc', 'TRUE')
INSERT INTO Services VALUES ('Cat001.02', 'Service 2 of Cat Lock ', 'Cat001', '100000', 'No desc', 'TRUE')
INSERT INTO Services VALUES ('Cat001.03', 'Service 3 of Cat Lock ', 'Cat001', '100000', 'No desc', 'TRUE')
INSERT INTO Services VALUES ('Cat002.01', 'Classic massage', 'Cat002', '150000', 'Our classic massage is based on traditional Swedish massage techniques', 'FALSE')
INSERT INTO Services VALUES ('Cat002.02', 'Aromatherapy massage', 'Cat002', '180000', 'A massage with blended oils to suit your well-being to calm, stimulate or balance.', 'FALSE')
INSERT INTO Services VALUES ('Cat002.03', 'Fitness massage', 'Cat002', '220000', 'Using an aromatherapy oil to warm and relax the muscles for a deeper and more focused pressure', 'FALSE')
INSERT INTO Services VALUES ('Cat002.04', 'Hot stone massage', 'Cat002', '250000', 'For the ultimate in relaxation this massage will combine the use of basalt hot stones', 'TRUE')
INSERT INTO Services VALUES ('Cat003.01', 'Jessica Manicure', 'Cat003', '50000', 'Includes custom colours, extra glossy, fast drying top coats, soak off gels, hand and cuticle care plus many other essentials to help maintain the health of the natural nail', 'FALSE')
INSERT INTO Services VALUES ('Cat003.02', 'ZenSpa Pedicure', 'Cat003', '70000', 'With over 200 stunning colours, styles and textures in the Jessica range', 'FALSE')
INSERT INTO Services VALUES ('Cat003.03', 'GELeration', 'Cat003', '100000', 'The beauty of Jessica with the strength of a gel', 'FALSE')
INSERT INTO Services VALUES ('Cat004.01', 'Manual application', 'Cat004', '150000', 'Treatment time 1 hour', 'FALSE')
INSERT INTO Services VALUES ('Cat004.02', 'Spray tan application', 'Cat004', '200000', 'Treatment time 15 minutes', 'FALSE')
INSERT INTO Services VALUES ('Cat005.01', 'Hy-Wax System', 'Cat005', '300000', 'Australian Bodycare Hy-Wax system uses a unique heater system that provides each client with their own dispenser', 'FALSE')
INSERT INTO Services VALUES ('Cat005.02', 'Sine', 'Cat005', '350000', 'The wax is flexible, allowing hair to be removed quickly and effectively with ease', 'FALSE')
go

-- Specialist Table
INSERT INTO Specialist VALUES('Emp0001', 'Cat002.01')
INSERT INTO Specialist VALUES ('Emp0001', 'Cat002.02')
INSERT INTO Specialist VALUES ('Emp0001', 'Cat002.03')
INSERT INTO Specialist VALUES ('Emp0001', 'Cat002.04')
INSERT INTO Specialist VALUES ('Emp0001', 'Cat003.01')
INSERT INTO Specialist VALUES ('Emp0001', 'Cat003.02')
INSERT INTO Specialist VALUES ('Emp0001', 'Cat003.03')
INSERT INTO Specialist VALUES ('Emp0001', 'Cat004.01')
INSERT INTO Specialist VALUES ('Emp0001', 'Cat004.02')
INSERT INTO Specialist VALUES ('Emp0001', 'Cat005.01')
INSERT INTO Specialist VALUES ('Emp0001', 'Cat005.02')
INSERT INTO Specialist VALUES ('Emp0002', 'Cat002.01')
INSERT INTO Specialist VALUES ('Emp0002', 'Cat002.02')
INSERT INTO Specialist VALUES ('Emp0002', 'Cat002.03')
INSERT INTO Specialist VALUES ('Emp0002', 'Cat002.04')
INSERT INTO Specialist VALUES ('Emp0002', 'Cat003.01')
INSERT INTO Specialist VALUES ('Emp0002', 'Cat003.02')
INSERT INTO Specialist VALUES ('Emp0002', 'Cat003.03')
INSERT INTO Specialist VALUES ('Emp0002', 'Cat004.01')
INSERT INTO Specialist VALUES ('Emp0002', 'Cat004.02')
INSERT INTO Specialist VALUES ('Emp0002', 'Cat005.01')
INSERT INTO Specialist VALUES ('Emp0002', 'Cat005.02')
INSERT INTO Specialist VALUES ('Emp0003', 'Cat002.01')
INSERT INTO Specialist VALUES ('Emp0003', 'Cat002.02')
INSERT INTO Specialist VALUES ('Emp0003', 'Cat002.03')
INSERT INTO Specialist VALUES ('Emp0003', 'Cat002.04')
INSERT INTO Specialist VALUES ('Emp0003', 'Cat003.01')
INSERT INTO Specialist VALUES ('Emp0003', 'Cat003.02')
INSERT INTO Specialist VALUES ('Emp0003', 'Cat003.03')
INSERT INTO Specialist VALUES ('Emp0003', 'Cat004.01')
INSERT INTO Specialist VALUES ('Emp0003', 'Cat004.02')
INSERT INTO Specialist VALUES ('Emp0003', 'Cat005.01')
INSERT INTO Specialist VALUES ('Emp0003', 'Cat005.02')
INSERT INTO Specialist VALUES ('Emp0004', 'Cat002.01')
INSERT INTO Specialist VALUES ('Emp0004', 'Cat002.02')
INSERT INTO Specialist VALUES ('Emp0004', 'Cat002.03')
INSERT INTO Specialist VALUES ('Emp0004', 'Cat002.04')
INSERT INTO Specialist VALUES ('Emp0004', 'Cat003.01')
INSERT INTO Specialist VALUES ('Emp0004', 'Cat003.02')
INSERT INTO Specialist VALUES ('Emp0004', 'Cat003.03')
INSERT INTO Specialist VALUES ('Emp0004', 'Cat004.01')
INSERT INTO Specialist VALUES ('Emp0004', 'Cat004.02')
INSERT INTO Specialist VALUES ('Emp0004', 'Cat005.01')
INSERT INTO Specialist VALUES ('Emp0004', 'Cat005.02')
INSERT INTO Specialist VALUES ('Emp0005', 'Cat002.01')
INSERT INTO Specialist VALUES ('Emp0005', 'Cat002.02')
INSERT INTO Specialist VALUES ('Emp0005', 'Cat002.03')
INSERT INTO Specialist VALUES ('Emp0005', 'Cat002.04')
INSERT INTO Specialist VALUES ('Emp0005', 'Cat003.01')
INSERT INTO Specialist VALUES ('Emp0005', 'Cat003.02')
INSERT INTO Specialist VALUES ('Emp0005', 'Cat003.03')
INSERT INTO Specialist VALUES ('Emp0005', 'Cat004.01')
INSERT INTO Specialist VALUES ('Emp0005', 'Cat004.02')
INSERT INTO Specialist VALUES ('Emp0005', 'Cat005.01')
INSERT INTO Specialist VALUES ('Emp0005', 'Cat005.02')
INSERT INTO Specialist VALUES ('Emp0006', 'Cat002.01')
INSERT INTO Specialist VALUES ('Emp0006', 'Cat002.02')
INSERT INTO Specialist VALUES ('Emp0006', 'Cat002.03')
INSERT INTO Specialist VALUES ('Emp0006', 'Cat002.04')
INSERT INTO Specialist VALUES ('Emp0006', 'Cat003.01')
INSERT INTO Specialist VALUES ('Emp0006', 'Cat003.02')
INSERT INTO Specialist VALUES ('Emp0006', 'Cat003.03')
INSERT INTO Specialist VALUES ('Emp0006', 'Cat004.01')
INSERT INTO Specialist VALUES ('Emp0006', 'Cat004.02')
INSERT INTO Specialist VALUES ('Emp0006', 'Cat005.01')
INSERT INTO Specialist VALUES ('Emp0006', 'Cat005.02')
INSERT INTO Specialist VALUES ('Emp0007', 'Cat002.01')
INSERT INTO Specialist VALUES ('Emp0007', 'Cat002.02')
INSERT INTO Specialist VALUES ('Emp0007', 'Cat002.03')
INSERT INTO Specialist VALUES ('Emp0007', 'Cat002.04')
INSERT INTO Specialist VALUES ('Emp0007', 'Cat003.01')
INSERT INTO Specialist VALUES ('Emp0007', 'Cat003.02')
INSERT INTO Specialist VALUES ('Emp0007', 'Cat003.03')
INSERT INTO Specialist VALUES ('Emp0007', 'Cat004.01')
INSERT INTO Specialist VALUES ('Emp0007', 'Cat004.02')
INSERT INTO Specialist VALUES ('Emp0007', 'Cat005.01')
INSERT INTO Specialist VALUES ('Emp0007', 'Cat005.02')
INSERT INTO Specialist VALUES ('Emp0008', 'Cat002.01')
INSERT INTO Specialist VALUES ('Emp0008', 'Cat002.02')
INSERT INTO Specialist VALUES ('Emp0008', 'Cat002.03')
INSERT INTO Specialist VALUES ('Emp0008', 'Cat002.04')
INSERT INTO Specialist VALUES ('Emp0008', 'Cat003.01')
INSERT INTO Specialist VALUES ('Emp0008', 'Cat003.02')
INSERT INTO Specialist VALUES ('Emp0008', 'Cat003.03')
INSERT INTO Specialist VALUES ('Emp0008', 'Cat004.01')
INSERT INTO Specialist VALUES ('Emp0008', 'Cat004.02')
INSERT INTO Specialist VALUES ('Emp0008', 'Cat005.01')
INSERT INTO Specialist VALUES ('Emp0008', 'Cat005.02')
go

-- Customer Table
INSERT INTO Customer(CustID,CustName, Gender, Addr, Email, DOB, UserCreated) VALUES ('Cust000001', 'Warent Mark', 'FALSE', 'Berlin', NULL, '01/10/1979', 'admin')
INSERT INTO Customer(CustID,CustName, Gender, Addr, Email, DOB, UserCreated) VALUES ('Cust000002', 'Linsay Lohan', 'TRUE', 'New York', 'lin@gmail.com', '05/22/1986', 'admin')
INSERT INTO Customer(CustID,CustName, Gender, Addr, Email, DOB, UserCreated) VALUES ('Cust000003', 'Tom Cruise', 'FALSE', 'California', 'tom@yahoo.com', '06/22/1975', 'admin')
INSERT INTO Customer(CustID,CustName, Gender, Addr, Email, DOB, UserCreated) VALUES ('Cust000004', 'Lex Luther', 'FALSE', 'Gotham', NULL, '06/14/2011', 'admin')
INSERT INTO Customer(CustID,CustName, Gender, Addr, Email, DOB, UserCreated) VALUES ('Cust000005', 'Min Change', 'TRUE', 'Bejing', NULL, '11/20/1963', 'admin')
INSERT INTO Customer(CustID,CustName, Gender, Addr, Email, DOB, UserCreated) VALUES ('Cust000006', 'Late Wong', 'TRUE', 'Bejing', NULL, '08/02/1975', 'admin')
INSERT INTO Customer(CustID,CustName, Gender, Addr, Email, DOB, UserCreated) VALUES ('Cust000007', 'Robert Calathan', 'FALSE', 'Paris', NULL, '03/16/1986', 'admin')
INSERT INTO Customer(CustID,CustName, Gender, Addr, Email, DOB, UserCreated) VALUES ('Cust000008', 'Saul Bathe', 'FALSE', 'Far far away', NULL, '06/17/1991', 'admin')
INSERT INTO Customer(CustID,CustName, Gender, Addr, Email, DOB, UserCreated) VALUES ('Cust000009', 'Lovely Princess', 'TRUE', 'Fairy tale', NULL, '12/12/1988', 'admin')
go

-- AppMaster Table

DECLARE @DAY1 varchar(8) =  format(getdate(),'yyyyMMdd')
DECLARE @DAY2 varchar(8) =  format(getdate()-1,'yyyyMMdd')
DECLARE @DAY3 varchar(8) =  format(getdate()-2,'yyyyMMdd')

INSERT INTO AppMaster(AppID, AppDate, Status, CustID, UserCreated) VALUES (@DAY3+'-App001', getDate()-2, 'Late-bill', 'Cust000003', 'admin')	
INSERT INTO AppMaster(AppID, AppDate, Status, CustID, UserCreated) VALUES (@DAY2+'-App001', getDate()-1, 'Late-bill', 'Cust000001', 'admin')	
INSERT INTO AppMaster(AppID, AppDate, Status, CustID, UserCreated) VALUES (@DAY2+'-App002', getDate()-1, 'Billed', 'Cust000002', 'admin')	
INSERT INTO AppMaster(AppID, AppDate, Status, CustID, UserCreated) VALUES (@DAY1+'-App001', getDate(), 'Complete', 'Cust000001', 'admin')	
INSERT INTO AppMaster(AppID, AppDate, Status, CustID, UserCreated) VALUES (@DAY1+'-App002', getDate(), 'Pre-bill', 'Cust000003', 'admin')	
INSERT INTO AppMaster(AppID, AppDate, Status, CustID, UserCreated) VALUES (@DAY1+'-App003', getDate(), 'Processing', 'Cust000004', 'admin')	
INSERT INTO AppMaster(AppID, AppDate, Status, CustID, UserCreated) VALUES (@DAY1+'-App004', getDate(), 'Billed', 'Cust000002', 'admin')	
INSERT INTO AppMaster(AppID, AppDate, Status, CustID, UserCreated) VALUES (@DAY1+'-App005', getDate(), 'Billed', 'Cust000005', 'admin')	
INSERT INTO AppMaster(AppID, AppDate, Status, CustID, UserCreated) VALUES (@DAY1+'-App006', getDate(), 'Processing', 'Cust000006', 'admin')	
INSERT INTO AppMaster(AppID, AppDate, Status, CustID, UserCreated) VALUES (@DAY1+'-App007', getDate(), 'Pre-bill', 'Cust000007', 'admin')	
INSERT INTO AppMaster(AppID, AppDate, Status, CustID, UserCreated) VALUES (@DAY1+'-App008', getDate(), 'Complete', 'Cust000008', 'admin')	
INSERT INTO AppMaster(AppID, AppDate, Status, CustID, UserCreated) VALUES (@DAY1+'-App009', getDate(), 'Processing', 'Cust000009', 'admin')	
go

-- AppDetails Table

DECLARE @DAY1 varchar(8) =  format(getdate(),'yyyyMMdd')
DECLARE @DAY2 varchar(8) =  format(getdate()-1,'yyyyMMdd')
DECLARE @DAY3 varchar(8) =  format(getdate()-2,'yyyyMMdd')

INSERT INTO AppDetails(AppDID, AppID, ServiceID, StaffID, Slot, Status, UserCreated) VALUES (@DAY3+'-App001.01', @DAY3+'-App001', 'Cat002.01', 'Emp0003', 1, 'Finished', 'admin')	
INSERT INTO AppDetails(AppDID, AppID, ServiceID, StaffID, Slot, Status, UserCreated) VALUES (@DAY3+'-App001.02', @DAY3+'-App001', 'Cat002.02', 'Emp0004', 2, 'Canceled', 'admin')	
INSERT INTO AppDetails(AppDID, AppID, ServiceID, StaffID, Slot, Status, UserCreated) VALUES (@DAY2+'-App001.01', @DAY2+'-App001', 'Cat002.03', 'Emp0005', 1, 'Finished', 'admin')	
INSERT INTO AppDetails(AppDID, AppID, ServiceID, StaffID, Slot, Status, UserCreated) VALUES (@DAY2+'-App001.02', @DAY2+'-App001', 'Cat002.01', 'Emp0003', 2, 'Canceled', 'admin')	
INSERT INTO AppDetails(AppDID, AppID, ServiceID, StaffID, Slot, Status, UserCreated) VALUES (@DAY2+'-App002.01', @DAY2+'-App002', 'Cat002.02', 'Emp0004', 2, 'Finished', 'admin')	
INSERT INTO AppDetails(AppDID, AppID, ServiceID, StaffID, Slot, Status, UserCreated) VALUES (@DAY2+'-App002.02', @DAY2+'-App002', 'Cat002.03', 'Emp0005', 1, 'Canceled', 'admin')	
INSERT INTO AppDetails(AppDID, AppID, ServiceID, StaffID, Slot, Status, UserCreated) VALUES (@DAY1+'-App001.01', @DAY1+'-App001', 'Cat002.01', 'Emp0003', 1, 'Finished', 'admin')	
INSERT INTO AppDetails(AppDID, AppID, ServiceID, StaffID, Slot, Status, UserCreated) VALUES (@DAY1+'-App001.02', @DAY1+'-App001', 'Cat002.02', 'Emp0004', 2, 'Canceled', 'admin')	
INSERT INTO AppDetails(AppDID, AppID, ServiceID, StaffID, Slot, Status, UserCreated) VALUES (@DAY1+'-App002.01', @DAY1+'-App002', 'Cat002.03', 'Emp0005', 1, 'Finished', 'admin')
INSERT INTO AppDetails(AppDID, AppID, ServiceID, StaffID, Slot, Status, UserCreated) VALUES (@DAY1+'-App002.02', @DAY1+'-App002', 'Cat002.01', 'Emp0003', 2, 'Canceled', 'admin')	
INSERT INTO AppDetails(AppDID, AppID, ServiceID, StaffID, Slot, Status, UserCreated) VALUES (@DAY1+'-App003.01', @DAY1+'-App003', 'Cat002.02', 'Emp0004', 1, 'Pending', 'admin')	
INSERT INTO AppDetails(AppDID, AppID, ServiceID, StaffID, Slot, Status, UserCreated) VALUES (@DAY1+'-App003.02', @DAY1+'-App003', 'Cat002.03', 'Emp0005', 2, 'Pending', 'admin')
INSERT INTO AppDetails(AppDID, AppID, ServiceID, StaffID, Slot, Status, UserCreated) VALUES (@DAY1+'-App003.03', @DAY1+'-App003', 'Cat002.01', 'Emp0003', 1, 'Finished', 'admin')	
INSERT INTO AppDetails(AppDID, AppID, ServiceID, StaffID, Slot, Status, UserCreated) VALUES (@DAY1+'-App003.04', @DAY1+'-App003', 'Cat002.02', 'Emp0004', 2, 'Finished', 'admin')	
INSERT INTO AppDetails(AppDID, AppID, ServiceID, StaffID, Slot, Status, UserCreated) VALUES (@DAY1+'-App003.05', @DAY1+'-App003', 'Cat002.01', 'Emp0005', 1, 'Canceled', 'admin')
INSERT INTO AppDetails(AppDID, AppID, ServiceID, StaffID, Slot, Status, UserCreated) VALUES (@DAY1+'-App003.06', @DAY1+'-App003', 'Cat003.02', 'Emp0003', 2, 'Canceled', 'admin')	
INSERT INTO AppDetails(AppDID, AppID, ServiceID, StaffID, Slot, Status, UserCreated) VALUES (@DAY1+'-App004.01', @DAY1+'-App004', 'Cat003.03', 'Emp0004', 1, 'Canceled', 'admin')	
INSERT INTO AppDetails(AppDID, AppID, ServiceID, StaffID, Slot, Status, UserCreated) VALUES (@DAY1+'-App004.02', @DAY1+'-App004', 'Cat003.01', 'Emp0005', 2, 'Finished', 'admin')
INSERT INTO AppDetails(AppDID, AppID, ServiceID, StaffID, Slot, Status, UserCreated) VALUES (@DAY1+'-App005.01', @DAY1+'-App005', 'Cat003.03', 'Emp0004', 1, 'Canceled', 'admin')	
INSERT INTO AppDetails(AppDID, AppID, ServiceID, StaffID, Slot, Status, UserCreated) VALUES (@DAY1+'-App005.02', @DAY1+'-App005', 'Cat003.01', 'Emp0005', 2, 'Finished', 'admin')	
INSERT INTO AppDetails(AppDID, AppID, ServiceID, StaffID, Slot, Status, UserCreated) VALUES (@DAY1+'-App006.01', @DAY1+'-App006', 'Cat003.01', 'Emp0005', 1, 'Pending', 'admin')
INSERT INTO AppDetails(AppDID, AppID, ServiceID, StaffID, Slot, Status, UserCreated) VALUES (@DAY1+'-App006.02', @DAY1+'-App006', 'Cat003.01', 'Emp0003', 2, 'Pending', 'admin')	
INSERT INTO AppDetails(AppDID, AppID, ServiceID, StaffID, Slot, Status, UserCreated) VALUES (@DAY1+'-App006.03', @DAY1+'-App006', 'Cat002.03', 'Emp0004', 1, 'Finished', 'admin')	
INSERT INTO AppDetails(AppDID, AppID, ServiceID, StaffID, Slot, Status, UserCreated) VALUES (@DAY1+'-App006.04', @DAY1+'-App006', 'Cat003.01', 'Emp0004', 2, 'Finished', 'admin')
INSERT INTO AppDetails(AppDID, AppID, ServiceID, StaffID, Slot, Status, UserCreated) VALUES (@DAY1+'-App006.05', @DAY1+'-App006', 'Cat003.01', 'Emp0005', 1, 'Canceled', 'admin')	
INSERT INTO AppDetails(AppDID, AppID, ServiceID, StaffID, Slot, Status, UserCreated) VALUES (@DAY1+'-App006.06', @DAY1+'-App006', 'Cat003.02', 'Emp0003', 2, 'Canceled', 'admin')	
INSERT INTO AppDetails(AppDID, AppID, ServiceID, StaffID, Slot, Status, UserCreated) VALUES (@DAY1+'-App007.01', @DAY1+'-App007', 'Cat002.03', 'Emp0005', 1, 'Finished', 'admin')
INSERT INTO AppDetails(AppDID, AppID, ServiceID, StaffID, Slot, Status, UserCreated) VALUES (@DAY1+'-App007.01', @DAY1+'-App007', 'Cat002.01', 'Emp0003', 2, 'Canceled', 'admin')	
INSERT INTO AppDetails(AppDID, AppID, ServiceID, StaffID, Slot, Status, UserCreated) VALUES (@DAY1+'-App008.01', @DAY1+'-App008', 'Cat002.01', 'Emp0003', 1, 'Finished', 'admin')	
INSERT INTO AppDetails(AppDID, AppID, ServiceID, StaffID, Slot, Status, UserCreated) VALUES (@DAY1+'-App008.02', @DAY1+'-App008', 'Cat002.02', 'Emp0004', 2, 'Canceled', 'admin')
go